Business Intelligence Project

12/01/2021

Teacher : Mister VASSIDIKI FOFANA

Team 1 : Sami Heddid – Errati Saad – Gilbert Delphin – Mayouran

Varathalingam – Nihale Ben Driouch

Promotion 2022, Team 1

INTRODUCTION

TO BUSINESS

INTELLIGENCE

Sami HEDDID

Errati SAAD

Gilbert DELPHIN

Mayouran

VARATHALINGAM

Nihale BEN DRIOUCH

Deadline :

12/01/2021

For :

M.VASSIDIKI

FOFANA

BUSINESS INTELLIGENCE REPORT

SUMMARY | | |

BUSINESS INTELLIGENCE PROJECT

I – Introduction

II – Project’s Description

III – Team Organization

IV – ER Diagram

V – Relational Model

VI – SSIS / SSAS

VII – SSRS

VIII - Conclusion

BUSINESS INTELLIGENCE REPORT

Screenshot showing what it looked like in the beginning.

I - INTRODUCTION

First and foremost, Team 1 would like to thank mister VASSIDIKI FOFANA for

his help throughout this project and his pedagogy during our TPs which helped us

greatly understand the principles of Business Intelligence. We are, indeed, in a crucial

year where understanding foundations and grounds of BI are key not only to an IT

engineer but also the enterprise he works in.

So, the course as well as the one teaching it is very important to us and we would

like to note it by showing our appreciation for it.

Being students in the major BUSINESS INTELLIGENCE, we’ll be constantly

manipulating data inside a database. Nevertheless, we must point out that we must

analyze these datas the best way possible to make the best solution possible. Every IT

company is involved with BI and DBs, so this project is a good practice to get us started.

NB : Way of Working

As agile methodologies can tend to be as important as BI itself inside a company, we decided to work

with the framework SCRUM, using the Atlassian Jira Software. That way, we can synchronize ourselves

and work more efficiently and finish our tasks in the deadline set.

BUSINESS INTELLIGENCE REPORT

II – PROJECT’S DESCRIPTION

Let’s summarize what is happening first.

The objective of the project is to enhance the international positioning of

EFREI PARIS. For this, the school signed partnerships with several major schools and

universities around the globe. it is about having a reporting tool that will allow him to

follow the students during their schooling but also in their professional life through the

alumni association of which he remains the sponsor. Using a robust data model and

management KPIs, the tool should also allow to follow various things like course

programming, monitoring the availability of teachers, end of year assessments and

exams, payment of tuition fees or salaries of school staffs.

III – TEAM ORGANIZATION

As this is quite the consequent project and we are considered as IT experts, we needed to

have roles for the team to achieve tasks more efficiently in agile.

PRODUCT OWNER : Sami Heddid. He’s the one updating the product backlog,

as well as defining which functionalities the team needed to do in priorities.

CUBE DEVELOPER : Gilbert Delphin. He’s the one creating the cube and

importing data from the database to have it in the data source.

DATABASE CREATOR : Mayouran Varathalingam. He’s the one creating the

database in SQL Management Studio.

REPORT WRITER : Nihale Ben Driouch. She’s the one writing the report.

DATA MODELS CREATOR : Saad Errati. He’s the one creating the DB models.

BUSINESS INTELLIGENCE REPORT

ER DIAGRAM

It must be pointed however, that there were some tasks which the team did together to be

sure no one was left behind : for example, creating the .txt files for the database, choosing

which tables to create and why, with which attributes, etc.

IV – ER DIAGRAM

An entity relationship diagram (ERD) shows the relationships of entity sets

stored in a database. An entity in this context is an object, a component of data. An en-

tity set is a collection of similar entities. These entities can have attributes that define

its properties. By defining the entities, their attributes, and showing the relationships

between them, an ER diagram illustrates the logical structure of databases.

ER diagrams are used to sketch out the design of a database. In our case, it was

very important for us to model our database as we are dealing with a big structure. And it

is partially thanks to modelization that the school could make better business decisions.

BUSINESS INTELLIGENCE REPORT

Reasoning behind choices :

The file project contains all the tables used in this project and based of the E/A

diagram.

Our main table is Student : we chose as a primary key to this table the

attribute StudentID, as explained in the description given by the professor,

Mister Meunier wants to follow the students during their schooling, this is why

we created the table Student with their name, the sex, the date of birth and the

grade as attributes.

Also he want to follow them in their professional life through the alumni

association, so we create a table Alumni with the name of their company.

We needed a table Course to allow us to see the courses of students but also

professors with their salaries directly related to the AdministrationStaff, with StaffID,

the primary Key.

The teacher table allows us to track the availability of teachers and thus allow

them to carry out their course.

We have also created a table with tuition fees for students with as primary key,

TuitionFeesId

M. Meunier wishes to know the need to open school canteens on his

campuses in London, Pekin and New York. He is therefore interested in the

attendance rate of the canteen of the Ivry and Marseille campuses to validate

an opening on another campus.

It is for this reason that we decided to create a Canteen and canteen Attendance

table in order to have the number of students each month in a canteen, this one is

directly connected to the campus with as primary keys Attendance ID.

BUSINESS INTELLIGENCE REPORT

RELATIONAL MODEL

V – RELATIONAL MODEL

The purpose of the relational model is to provide a declarative method for

specifying data and queries : users directly state what information the database

contains and what information they want from it, and let the database management

system software take care of describing data structures for storing the data and retrieval

procedures for answering queries. The relational model is the primary method for

organizing and maintaining data today in information systems because it is so flexible

and accessible. It organizes data in two-dimensional tables called relations, with rows

and columns.

BUSINESS INTELLIGENCE REPORT

SSIS CONTROL FLOW

VI – SSIS / SSAS

a) SSIS Part

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL

Server database software that can be used to perform a broad range of data mi-

gration tasks.

SSIS is a platform for data integration and workflow applications. It features

a data warehousing tool used for data extraction, transformation, and loading

(ETL). The tool may also be used to automate maintenance of SQL Server da-

tabases and updates to multidimensional cube data.

In SSIS, we needed to take care of two major tasks : Control Flow and Data

Flow. Control flow defines a workflow of tasks to be executed, such as follows :

Indeed, as we’re working with 13 tables, we needed to link them all to the

database with Connection Managers.

BUSINESS INTELLIGENCE REPORT

SSIS DATA FLOW FOR COURSE TABLE

.txt FILE FORMAT OF THE COURSE TABLE

After that, we needed to input data flow tasks for every tables : they had to have a

Source, and a Destination for them to be browsed properly. Here’s an example with the

Course table :

NB : We must point out that we dealt with our tables by creating them in .txt format.

Subsequently, when asked for the source of the table, we browsed in with tables we

created before-hand. Here’s an example with the same Course table :

BUSINESS INTELLIGENCE REPORT

FLAT FILE CONNECTION MANAGER EDITOR, WHERE WE BROWSE OUR .txt FILES

b) SSAS Part :

After storing all of our data in exploitable form, we could start the

Multidimensional Analysis. To do this, SSAS comes in handy.

Microsoft SQL Server Analysis Services, SSAS, is an online analytical

processing (OLAP) and data mining tool in Microsoft SQL Server. SSAS is used

as a tool by organizations to analyze and make sense of information possibly

spread out across multiple databases, or in disparate tables or

files. Microsoft has included a number of services in SQL Server related

to business intelligence and data warehousing. These services include

Integration Services, Reporting Services and Analysis Services. Analysis

Services includes a group of OLAP and data mining capabilities and comes in

two flavors - Multidimensional and Tabular.

Furthermore, and as reminded in the Project’s Description, we needed to

follow :

1) Course Programming

2) Monitoring of teachers

3) Monitoring of asessments

4) Payment of tuition fees

5) Payment of salaries

BUSINESS INTELLIGENCE REPORT

DIMENSIONS OF THE MULTIDIMENSIONAL

CUBE

ALUMNI RESULTS

Hence why, as we are working with a Multidimensional Cube, we decided

the create dimensions based on these datas :

After deploying the cube, it appears on Management Studio. Now, we can

analyze whichever results we wants :

BUSINESS INTELLIGENCE REPORT

ASSESSMENTS RESULTS FILTERED BY EXAM (NB : Those who don’t have grades is because they

didn’t pass the exam yet.)

ASSESSMENTS RESULTS FILTERED BY PROJECT

PAYMENT OF SALARIES RESULT

BUSINESS INTELLIGENCE REPORT

TEACHER AVAILABILITY RESULT

PAYMENT OF TUITION FEES

BUSINESS INTELLIGENCE REPORT

REPORTS FILES

VI – SSRS

After that, all was left was to do the reporting part. We must point out that we tried

to implement it with PowerBI, but we couldn’t get the database to work as we can’t

restore a database from a recent version to a previous one. Instead, we decided to work

with SSRS.

SQL Server Reporting Services (SSRS) is a server-based report generating software

system from Microsoft. It is part of a suite of Microsoft SQL Server services, including

SSAS (SQL Server Analysis Services) and SSIS (SQL Server Integration Services).

Administered via a Web interface, it can be used to prepare and deliver a variety of in-

teractive and printed reports. The SSRS service provides an interface into Microsoft Vi-

sual Studio so that developers as well as SQL administrators can connect to SQL data-

bases and use SSRS tools to format SQL reports in many complex ways. It also provides

a 'Report Builder' tool for less technical users to format SQL reports of lesser com-

plexity.

SSRS competes with Crystal Reports and other business intelligence tools.

Here, we created 7 reports from the Data Source of our databases :

BUSINESS INTELLIGENCE REPORT

ALUMNI REPORT

ASSESSMENTS REPORT (NB : Those who don’t have grades is because they didn’t pass the

assessment yet.)

BUSINESS INTELLIGENCE REPORT

CANTEEN ATTENDANCE REPORT

TEACHER AVAILABILITY REPORT

BUSINESS INTELLIGENCE REPORT

PAYMENT OF SALARIES REPORT

PAYMENT OF TUITION FEES REPORT

Through these reports and throughout the entire process of analyzing our data, we

decided to choose measures which we found the most interesting and observed them

through different dimensions. After that, we modelized those with differents kind of

charts. To do this in SSRS, we needed to click on the toolbox that would let us customize

our reports :

BUSINESS INTELLIGENCE REPORT

CHART CUSTOMIZATION IN SSRS

BUSINESS INTELLIGENCE REPORT

VII – CONCLUSION

This project was very interesting as it was the first one to formally introduce us to Business

Intelligence. It’s a good thing we work in a team of five as we knew exactly how to divide

tasks and we always helped one another when there was some issues regarding the

modelling, developing or even working in agile : communication was indeed key.

What was most interesting is that multiple answers exist as to how to operate inside MSSQL

and how to modelize tasks (how to even do BI), so we talked a lot about how we should

approach things.

Again, we would like to thank mister VASSIDIKI FOFANA for this help throughout his course.

Teaching and following courses can somehow be difficult with the pandemic but he always

answered our questions efficiently and we highly appreciate it.

(NB : Every file we showed in the report is in the .zip file, don’t hesitate to read the readme.txt

file to make sure you have everything in hands.

Sami Heddid

Gilbert Delphin

Mayouran Varathalingam

Saad Errati

Nihale Ben Driouch

Promotion 2022, Efrei Paris